<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    
</body>
</html>
<script>
    /*SQL自动查询函数*/
    //source_field_arr, source_table_arr,save_arr, queryStatus::0未开始:1查询中:2返回结果
    var do_sql_field_id = "field249445";
    var get_sqlResult_field_id = "field249446";
    var source_field_arr;//字段名数组
    var source_table_arr;//表名数组
    var source_table_showname_arr;//表名数组
    var save_arr;//保存到数组
    var queryStatus = 0;//0未开始:1查询中:2返回结果
    var index = 0;//查询的次数
    let index_max = 0;//查询的最大次数
    
    var interval_1 ; //查询定时器
    var interval_2 ; //查询定时器
    
    var autoQueryField_ID = "field249443"; //一开始启动查询的字段ID
    var lastAutoQueryField_Value ; //一开始启动查询的字段取值,即上一次查询内容
    var triggerAutoQueryField_ID = "field249444";//触法自动查询的单选框字段ID
    
    var saveFinallyResult_ID_2= "field249447";//显示每一次查询结果
    
    var sourceDataBase = "local";
    
    let lastQueryEnd;//上一次查詢是否結束
    
    let tipsField_ID = "field249448";//输出执行日志的字段ID
    
    let last_query_result;
    
    //启动查询,后面每一步自动执行: 启动定时器
    WfForm.bindFieldChangeEvent(autoQueryField_ID,
        function(obj,id,value) {
            if(value == lastAutoQueryField_Value && index > 0) {
                WfForm.showMessage( "查询已进行中", 2, 3);
            }else if(value != lastAutoQueryField_Value && index > 0 ) {
                WfForm.showMessage( "请等待上一次查询结束", 2, 3);
                WfForm.changeFieldValue( id , {value: lastAutoQueryField_Value});
            }else {
                lastAutoQueryField_Value = 0;//更新上一次的值
                interval_1 = window.setInterval("doTriggerQuery()","0");//启动定时器
                interval_2 = window.setInterval("check_query_result()","3000");//启动定时器
                WfForm.showMessage( "查询开始...", 2, 3);
            }
        }
    );
    //定时触发下一次查询（真正的触发）：改变单选的值
    function doTriggerQuery() {
        //triggerAutoQueryField_ID
        if(queryStatus == 0 && lastQueryEnd != "ing" ) {
            WfForm.showMessage( "查询ing...", 2, 2);
            lastQueryEnd = "ing";//查询进行中
            if(WfForm.getFieldValue( triggerAutoQueryField_ID ) == 0) {
                WfForm.changeFieldValue( triggerAutoQueryField_ID , {value: 1 });
            }else {
                WfForm.changeFieldValue( triggerAutoQueryField_ID , {value: 0 });
            }
        }
    }
    function check_query_result() {
        //没有数据时，自动跳过
        if(queryStatus == 1 ) {
            let current_query_value = WfForm.getFieldValue(get_sql_result_field_id);
            if( current_query_value != null && last_query_result == current_query_value ) {
                last_query_result = current_query_value;
                let str_sql = WfForm.getFieldValue(do_sql_field_id) + "$没有数据，自动跳过$";
                WfForm.changeFieldValue(get_sql_result_field_id, {value : str_sql});
            }
        }
    }
    //（调用一次查询）:单选框触发后
    WfForm.bindFieldChangeEvent(triggerAutoQueryField_ID,function(obj,id,value) {
        if(value == '')
            return;
        queryAutoMain();
    });
    //设置查询内容，方式
    function queryAutoMain(){
        //alert("main");
        let queryTypeValue = WfForm.getFieldValue( autoQueryField_ID );
        if(queryTypeValue == 0) {
            queryAllTable("local");
        }else if(queryTypeValue == 1) {
            query_form_count("local");
        }
    }
    function queryAllTable(sourceDataBase) {
        //alert("save_arr:"+save_arr);
        //alert("&&table:"+source_table_arr);
        //第一步，查询所有表单名称，并保存到表单数组里面
        if(save_arr == null && source_table_arr == null) {
            if(sourceDataBase == "local") {			
                let str_sql = "select t.table_name || ';' from user_tables t";
                doQuery(str_sql,do_sql_field_id);		
            }
        }
        //第二步，根据表单名称，查询表单数据行数
        else if( source_table_arr != null && index < source_table_arr.length) {
            //alert("second");
            let str_sql = "select \'"+source_table_arr[index]+",\' || count(*) || ';' from "+ source_table_arr[index];
            //alert(str_sql);
            doQuery(str_sql,do_sql_field_id);
        }else {
            //转换格式
            let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換格式";
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
            //转换表名
            if(source_table_arr == null) {
                let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換表單";
                WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
                source_table_arr = save_arr.split(";");
                let arr_0 = source_table_arr[0] + "";
                //alert("source_table_arr[0]="+source_table_arr[0]);
                source_table_arr[0] = arr_0.substring(arr_0.indexOf("undefined")+9,arr_0.length);
                //alert("source_table_arr[0]="+source_table_arr[0]);
                save_arr = null;
                //alert("tableLen:"+source_table_arr.length);
                WfForm.showMessage( "查询表单名称结束", 2, 4);
                //重置index
                index = 0;
                lastQueryEnd = "ok";//查询jie
            }else if(index == source_table_arr.length) {
                //转换表的长度, 查询结束
                let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換長度";
                WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
                source_field_arr = save_arr.split(";");
                WfForm.showMessage( "查询表单长度结束", 2, 4);
                window.clearInterval(interval_1);//停止执行setInterval循环。
                //重置index
                index = 0;
                lastQueryEnd = "ok";//查询jie
            }
        }
        
    }
    //每隔几秒执行一次
    //一次查询
    function doQuery(str_sql, do_sql_field_id) {
        if(queryStatus == 0) {
            //清除中间结果
            WfForm.changeFieldValue( get_sqlResult_field_id , {value:'.'});
            let tips = "第"+(index+1)+"次查詢开始";//tishi
            //alert(str_sql);
            WfForm.showMessage( tips, 2, 4);//tishi
            let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + tips;
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
    
            WfForm.changeFieldValue( do_sql_field_id , {value:str_sql});
            queryStatus = 1;
        }else {
            WfForm.showMessage( "上一次查询未结束", 2, 4);
        }
    }
    function saveOneQuery(get_sqlResult_field_id, saveFinallyResult_ID_2, value) {
        if(queryStatus == 2) {
            let tips = "第"+(index+1)+"次查询结束";//tishi
            WfForm.showMessage( tips, 2, 4);//tishi
            let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + tips;
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
    
            tips = "第"+(index+1)+"次保存开始";//tishi
            WfForm.showMessage( tips, 2, 4);//tishi
            logQ = WfForm.getFieldValue(tipsField_ID) +"#" + tips;
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
    
            let value_str = value + "";
            save_arr += value_str;
            value_str = WfForm.getFieldValue( saveFinallyResult_ID_2 ) + value_str;
            WfForm.changeFieldValue( saveFinallyResult_ID_2 , {value:value_str});
            //清除中间结果
            //WfForm.changeFieldValue( get_sqlResult_field_id , {value:'.'});
    
            queryStatus = 0;
            //更新index
            index++;
            tips = "第"+(index)+"次保存结束";//tishi
            WfForm.showMessage( tips, 2, 4);//tishi
            logQ = WfForm.getFieldValue(tipsField_ID) +"#" + tips;
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
            lastQueryEnd = "ok";
        }else {
            WfForm.showMessage( "上一次查询结果未到", 2, 4);
        }
    }
    
    //查询结束后触发保存函数调用
    WfForm.bindFieldChangeEvent(get_sqlResult_field_id,function(obj,id,value) {
        //alert("sava change");
        if(value == '.' || value == '')
            return;
        /*if(value == ''){
            WfForm.changeFieldValue( get_sqlResult_field_id , {value:'没有数据;'});
        }*/
        queryStatus = 2;
        saveOneQuery(get_sqlResult_field_id, saveFinallyResult_ID_2, value);
    });
    /**所有表單發單量*/
    let form_name_arr;
    let form_count_arr;
    /*
    WfForm.bindFieldChangeEvent("field252442",    
        function(obj,id,value) {
            if(value == '')
                return;
            let value_str = value + "";
                form_name_arr = value_str.split(";");
                alert(form_name_arr.length+":last:"+form_name_arr[form_name_arr.length - 1]+"first:"+form_name_arr[0]);
        }
    );
    */
    
    function query_form_count(sourceDataBase) {
        //alert("save_arr:"+save_arr);
        //alert("&&table:"+source_table_arr);
        //第一步，查询所有表单名称，并保存到表单数组里面
        if(save_arr == null && source_table_arr == null){
            if(sourceDataBase == "local")
            {	//alert("save_arr:"+save_arr);		
                let str_sql = "select a.workflowname || ',' || b.tablename || ',' || a.isvalid ||  ';' from workflow_base a, workflow_bill b where a.formid = b.id";
                doQuery(str_sql,do_sql_field_id);		
            }
        }else if( source_table_arr != null && index < source_table_arr.length) {
            //第二步，根据表单名称，查询表单数据行数
            //alert("second");
            let str_sql = "select   \'<tr><td> "+source_table_showname_arr[index]+" </td>\'  ||  \' <td>"+source_table_arr[index]+"</td>\' ||  \'<td>\' ||  count(*) || '</td></tr>' from "+ source_table_arr[index];
            //alert(str_sql);
            doQuery(str_sql,do_sql_field_id);
        }
        //转换格式
        else
        {
            let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換格式";
            WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
            //转换表名
            if(source_table_arr == null)
            {
                let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換表單";
                WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
                
                let arr = save_arr.split(";");
                let source_table_arr_temp = "" ;
                let source_table_showname_arr_temp = "" ;
                for(let i = 0; i < arr.length; i++)
                {
                    let arr_d = (arr[i] + "").split(",");
                                     if(arr_d[2] == 1)
                                     {
                             source_table_arr_temp += (arr_d[1] + ";");
                            source_table_showname_arr_temp += (arr_d[0] + ";");
                                     }
                }
                            source_table_arr = source_table_arr_temp.split(";");		
                            source_table_showname_arr = source_table_showname_arr_temp.split(";");
    
                let arr_0 = source_table_arr[0] + "";
                //alert("source_table_arr[0]="+source_table_arr[0]);
                source_table_arr[0] = arr_0.substring(arr_0.indexOf("undefined")+9,arr_0.length);
                //alert("source_table_arr[0]="+source_table_arr[0]);
                save_arr = null;
        
                //alert("tableLen:"+source_table_arr.length);
                WfForm.showMessage( "查询表单名称结束", 2, 4);
                //重置index
                index = 0;
                lastQueryEnd = "ok";//查询jie
            }
            //转换表的长度, 查询结束
            else if(index == source_table_arr.length)
            {
                let logQ = WfForm.getFieldValue(tipsField_ID) +"#" + "進入轉換長度";
                WfForm.changeFieldValue(tipsField_ID, {value: logQ } );
                source_field_arr = save_arr.split(";");
                WfForm.showMessage( "查询表单长度结束", 2, 4);
        
                window.clearInterval(interval_1);     //停止执行setInterval循环。
                //重置index
                index = 0;
                lastQueryEnd = "ok";//查询jie
            }
        }
    }
    
    //用瀏覽框查詢內容  //查询某个數據庫表单所有数据
    //field263442
    //field263443
    var sql_Query_setence;
    WfForm.bindFieldChangeEvent("field263442",    
        function(obj,id,value)
        {
            if(value == '')
                return;
                    //alert(value);
                   let value_str = value + "";
                    let value_str_2 = value_str + "";
                   let table_name = WfForm.getFieldValue("field263443");
                   let value_arr = value_str.split(",");
                    value_str = value_str.replace(   /,/g , "  || '</pre></td><td>' ||  ");
    //alert(value_str);
    
                     sql_Query_setence = "<tr><td><pre>" + value_str_2.replace(   /,/g , "</pre></td><td>")+ "</pre></td></tr>" ;
    
                    value_str = "select  '<tr><td><pre>' ||  " + value_str + " || '</pre></td></tr>' " ;
                    
                    value_str += (" from " + table_name  );
    
                    WfForm.changeFieldValue( "field264443", {value: value_str  });
    
        }
    );
    
    //顯示表格
    
    WfForm.bindFieldChangeEvent("field264444",    
        function(obj,id,value)
        {
            if(value == '')
                return;
                   let parentNode_LL = document.getElementById("field264453").parentNode.parentNode;
                   parentNode_LL.innerHTML = parentNode_LL.innerHTML + "<table border><tbody>" + sql_Query_setence  + WfForm.getFieldValue("field264444") + "</tbody></table>";
                    alert(value);
                   
        }
    );
    //导出Excel
    //field269942
    
    WfForm.bindFieldChangeEvent("field269942",    
        function(obj,id,value)
        {
     alert(value);
            if(value == '')
                return;
                  alert("4");
                   //tableToExcel();
        }
    );
    
    /*
     function tableToExcel(){
     //要导出的json数据
    const jsonData = [
    {
    name:'路人甲',
     phone:'123456789',
     email:'000@123456.com'
     },
    {
    name:'炮灰乙',
     phone:'123456789',
     email:'000@123456.com'
     },
     {
    name:'土匪丙',
     phone:'123456789',
    email:'000@123456.com'
     },
     {
    name:'流氓丁',
     phone:'123456789',
     email:'000@123456.com'
     },
     ]
     //列标题，逗号隔开，每一个逗号就是隔开一个单元格
     let str = '姓名,电话,邮箱\n';
    //增加\t为了不让表格显示科学计数法或者其他格式
     for(let i = 0 ; i < jsonData.length ; i++ ){
     for(let item in jsonData[i]){
     str+='${jsonData[i][item] + '\t'},';
     }
    str+='\n';
     }
     //encodeURIComponent解决中文乱码
     let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
     //通过创建a标签实现
     let link = document.createElement("a");
     link.href = uri;
     //对下载的文件命名
     link.download = "json数据表.csv";
     document.body.appendChild(link);
     link.click();
     document.body.removeChild(link);
    }
    */
    
    WfForm.bindFieldChangeEvent("field226942",    
        function(obj,id,value)
        {
            if(value == '')
                return;
                let value_str = value +"";
                let baseConditions = null;
                let andConditons = null;
                let condtions_Field_ID_Arr = [282967];
                let base_sql_Field_ID = "field280949";
                if(value_str.indexOf("where") >= 0)
                {
                    baseConditions = value_str.split("where");
                }
                else
                    baseConditions =  [ value_str ];
                    
    
                if(baseConditions.length == 2)
                {
                    if(baseConditions[1].indexOf("and") >= 0)
                    {
                        andConditons =  baseConditions[1].split("and");
                    }
                }
                
    
                WfForm.changeFieldValue( base_sql_Field_ID , {value: baseConditions[0] });
    
                for(let i = 0; i < baseConditions.length && i < 6; i++ )
                {
                    let str_id = "field" +(condtions_Field_ID_Arr[0] + i );
                    WfForm.changeFieldValue( str_id , {value: " and " +andConditons[i] });
                }
    
                WfForm.changeFieldValue( "field282967" , {value:  " where "+andConditons[0] });
                   
                   
                    //alert(value);
                   
        }
    );
    WfForm.bindFieldChangeEvent("field328942",    
        function(obj,id,value)
        {
            if(value == '' || value == 0 )
                return;
                     let confirmStatus = confirm("該人員已在公司實習期大於或等於6個月，HR核薪將直接調整至試用期後薪資，用人單位可於HR調整後薪資上進行再次調整，因該人員將直接轉為正式員工，後期薪酬管理課將不在開立《試用期轉正調薪單》，請仔細閱讀此說明，謝謝") + "" ;
                  
                   if(confirmStatus  == "true")
                           confirmStatus  = 1;
                   else
                        confirmStatus   = 0;
            WfForm.changeFieldValue( "field328942" , {value:  confirmStatus   }); 
        }
    );
</script>    